OSIAS MAIA DA SILVA JUNIOR
BANCO DE DADOS
LISTA DE EXERCICIOS  2
osias@hotmail.com
osias@mailbr.com.br
calangodoido@hotmail.com

-------------------------
Criacao das tabelas
-------------------------


CREATE TABLE EDITORAS{
	ID_EDITORAS	INTEGER		NOT NULL,
	NOME_EDITORA	CHAR(20)	NOT NULL,
	CIDADE_EDITORA	CHAR(20)	NOT NULL,
	SIGLA_ESTADO_EDITORA	CHAR(2)	NOT NULL
}

CREATE TABLE TITULOS{
	ID_TITULO	INTEGER	  NOT NULL,
	ID_EDITORA	INTEGER	  NOT NULL,
	DESC_TITULO	CHAR(100) NOT NULL,
	DESC_ASSUNTO	CHAR(100),
	PRECO_UNITARIO	NUMBER(19,2),
	DT_PUBLICACAO	DATE 	NOT NULL
}

CREATE TABLE AUTORES{
	ID_AUTOR	INTEGER	  NOT NULL,
	NOME_AUTOR	CHAR(100) NOT NULL,
	SOBRENOME_AUTOR	CHAR(100) NOT NULL,
	NUM_TELEFONE	CHAR(100),
	CIDADE_AUTOR    CHAR(20)  NOT NULL,     	
	SIGLA_ESTADO_AUTOR	CHAR(2)	NOT NULL
}

CREATE TABLE AUT_TIT{
	ID_AUTOR	INTEGER	  NOT NULL,
	ID_TITULO	INTEGER	  NOT NULL,
	NUM_ORDEM_AUTOR	INTEGER	  NOT NULL,
	PERC_ROYALITIES_AUTOR	DOUBLE	  NOT NULL
}

CREATE TABLE LOJAS{
	ID_LOJA 	INTEGER	  NOT NULL,
	NOME_LOJA	CHAR(100) NOT NULL,
	RUA_LOJA	CHAR(20)	NOT NULL,
	CIDADE_LOJA	CHAR(20)	NOT NULL,
	SIGLA_ESTADO_LOJA CHAR(2)	NOT NULL
}

CREATE TABLE VENDAS{
	ID_LOJA 	INTEGER	  NOT NULL,
	ID_TITULO	INTEGER	  NOT NULL,
	QT_VENDIDA_TITULO INTEGER,
	DT_VENDA        DATE,
	FORMA_PAGAMNETO CHAR(20) NOT NULL,
	NUM_ORDEM_COMPRA INTEGER NOT NULL,
	VL_DESCONTO     NUMBER(19,2)
}


-------------------------
Resolucao dos Exercicios
-------------------------

1)
SELECT ID_TITULO, PRECO_UNITARIO
FROM   TITULOS
WHERE  PRECO_UNITARIO>20
  AND  DESC_ASSUNTO ="PSICOLOGIA" ;


2)
SELECT ID_TITULO, DESC_TITULO,DESC_ASSUNTO, PRECO_UNITARIO
FROM   TITULOS
WHERE  DESC_TITULO>="C"
  AND  DESC_TITULO< "G"
ORDER BY DESC_ASSUNTO, PRECO_UNITARIO DESC;

3)
SELECT 	ID_TITULO AS "Cdigo",
	DESC_TITULO AS "Ttulo",
	(SUM(QT_VENDIDA)*PRECO_UNITARIO ) AS "Faturamento"
FROM	TITULOS, VENDAS
WHERE	VENDAS.ID_TITULO = TITULOS.ID_TITULO
GROUP BY VENDAS.ID_TITULO

4)
SELECT  DESC_TITULO
  FROM  TITULOS
 WHERE  PRECO_UNITARIO = NULL;

5)
SELECT  DESC_TITULO,  PRECO_UNITARIO as "Preco Atual",
        ( PRECO_UNITARIO*1.15) AS "Preco Projetado"
  FROM  TITULOS
 WHERE DESC_ASSUNTO = "INFORMATICA"
    OR DESC_ASSUNTO = "DIREITO"
    OR DESC_ASSUNTO = "NUTRICAO";

6)
SELECT  DESC_TITULO,DT_PUBLICACAO
FROM    TITULOS
WHERE   DT_PUBLICACAO = 
	SELECT MAX(DT_PUBLICACAO) 
	FROM TITULOS;

7)
SELECT  ID_EDITORA, NOME_EDITORA, PRECO_UNITARIO
FROM    TITULOS, EDITORAS
WHERE   PRECO_UNITARIO = 
	SELECT MIN(PRECO_UNITARIO)
	FROM TITULOS;

8)
SELECT NOME_EDITORA, AVG(PRECO_UNITARIO) AS MEDIA
FROM   EDITORAS, TITULOS
WHERE  TITULOS.ID_EDITORA = EDITORAS_ID_EDITORA
AND    MEDIA>50 
AND    MEDIA<=130
GROUP BY NOME_EDITORA;

9)
SELECT SUM(QT_VENDIDA), NOME_LOJA
FROM LOJAS,VENDAS
WHERE LOJAS.ID_LOJA =VENDAS.ID_LOJA
GROUP BY NOME_LOJA;

10)
SELECT DESC_TITULO
FROM TITULOS, EDITORAS
WHERE SIGLA_ESTADO_EDITORA = "ES"
AND TITULOS.ID_EDITORA = EDITORAS.ID_EDITORA
AND PRECO_UNITARIO > 
	SELECT MAX(PRECO_UNITARIO)
	FROM TITULOS 
	WHERE SIGLA_ESTADO_EDITORA <> "ES"
	


11)
SELECT DESC_TITULO, ASSUNTO AS "Assunto", 
	MAX(PRECO_UNITARIO) AS "Maior Valor",
	MIN(PRECO_UNITARIO) AS "Menor Valor"
FROM TITULOS
GROUP BY ASSUNTO

12)
SELECT NOME_AUTOR , SOBRENOME_AUTOR , CIDADE_AUTOR
FROM   AUTORES
WHERE CIDADE_AUTOR IN
	(SELECT DISTINCT CIDADE_EDITORA
	FROM   EDITORAS)
ORDER BY NOME_AUTOR DESC

13)
SELECT NOME_AUTOR, SOBRENOME_AUTOR, DESC_TITULO, NOME_EDITORA
FROM 	AUTORES, TITULOS, EDITORAS, AUT_TITULO
WWHRE NOME_AUTOR LIKE "%IRA"
AND 	TITULOS.ID_TITULO = AUT_TIT.ID_TITULO
AND	AUTORES.ID_AUTOR = AUT_TIT.ID_AUTOR
AND	TITULOS.ID_EDITORA = EDITORAS.ID_EDITORA


14)
SELECT NOME_AUTOR
FROM   AUTORES, AUT_TIT, TITULOS 
WHERE TITULOS.ID_TITULO = AUT_TIT.ID_TITULO
AND	AUTORES.ID_AUTOR = AUT_TIT.ID_AUTOR
AND   AUT_TIT.ID_TITULO	NOT IN
	(
		SELECT DISTINCT ID_TITULO
		FROM VENDAS 
	)

15)
SELECT NOME_AUTOR, SOBRENOME_AUTOR, DESC_TITULO
FROM   AUTORES, AUT_TIT, TITULOS 
WHERE TITULOS.ID_TITULO = AUT_TIT.ID_TITULO
AND	AUTORES.ID_AUTOR = AUT_TIT.ID_AUTOR
AND   NOME_AUTOR LIKE "Moac[yi]r"

16)
SELECT NOME_LOJA, CIDADE_LOJA, QT_VENDIDA_TITULO
FROM   LOJAS,  TITULOS , VENDAS
WHERE  DESC_TITULO = "Banco de Dados"
  AND  VENDAS.ID_LOJA = LOJAS.ID_LOJA,
  AND  VENDAS.ID_TITULO = TITULOS.ID_TITULO
  AND  VENDAS.QT_VENDIDA_TITULO = 
	(
	SELECT MAX(QT_VENDIDA_TITULO)
	FROM   VENDAS 
	WHERE DESC_TITULO = "Banco de Dados"
	  AND  VENDAS.ID_LOJA = LOJAS.ID_LOJA,
	  AND  VENDAS.ID_TITULO = TITULOS.ID_TITULO
	)

17)
SELECT DESC_TITULO
FROM   TITULOS
WHERE  ID_TITULO NOT IN
	(
	SELECT DISTINCT ID_TITULO
	FROM   VENDAS
	)

18)
SELECT  NOME_LOJA 
FROM    LOJAS, VENDAS, TITULOS
WHERE   VENDAS.ID_LOJA = LOJAS.ID_LOJA
  AND   VENDAS.ID_TITULO = TITULOS.ID_TITULO
  AND	ID_TITULOS IN (
	SELECT ID_TITULO
	FROM TITULOS
	WHERE DESC_ASSUNTO = "Informatica"
	)
  AND	ID_TITULOS NOT IN (
	SELECT ID_TITULO
	FROM TITULOS
	WHERE DESC_ASSUNTO = "Turismo"
	)


19)
DELETE FROM TITULOS 
WHERE DT_PUBLICACAO <"20/10/1950"
  AND DESC_TITULO = "Esportes"

20)
UPDATE TITULOS
SET (PRECO_UNITARIO = PRECO_UNITARIO *1.3)
WHERE DESC_ASSUNTO = "INFORMATICA"

21)
CREATE VIEW AS ACERVO{
	SELECT  DESC_TITULO AS TITULO,
		NOME_AUTOR AS AUTOR,
		NOME_EDITORA AS EDITORA
	FROM 	TITULOS, EDITORAS, AUTORES, AUT_TIT
	WHERE 	TITULOS.ID_EDITORA = EDITORAS.ID_EDITORA
	AND	TITULOS.ID_TITULO = AUT_TIT.ID_TITULO
	AND	AUTORES.ID_AUTOR = AUT_TIT.ID_AUTOR
	
}







